
-- **************************************************************************
--    Project Name:   操作管理-中转错发率汇总表
--    Job Name:       jms_dm.dm_transfer_mistake_send_sum_dt
--    Author :        季修魁
--    date：          2023/08/30
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

insert overwrite table jms_dm.dm_transfer_mistake_send_sum_dt  partition (dt)
select date_time
       ,center_code
       ,center_name
       ,agent_code
       ,agent_name
       ,ordersource_name
       ,mistake_cnt
       ,operate_cnt
       ,nofst_code_cnt
       ,fst_code_no_destination_cnt
       ,no_route_cnt
       ,dispatch_cnt
       ,temp_tran_cnt
       ,fstcode_err_cnt
       ,up_bagging_err_cnt
       ,equipment_factor_cnt
       ,return_cnt_cnt
       ,send_on_cnt
       ,delete_scan_cnt
       ,human_factor_cnt
       ,err_load_cnt
       ,virt_code
       ,virt_name
       ,provider_id
       ,provider_desc
       ,dt
  from (select 
            date_time
            ,center_code --中心编码
            ,center_name as center_name --中心名称
            ,agent_code  as agent_code --代理区code
            ,agent_name  as agent_name --代理区
            ,virt_code   as virt_code
            ,virt_name   as virt_name
            ,ordersource_name --订单来源
            ,count(bill_Code)                  as operate_cnt                   --操作量
            ,count(if(is_mistake=1,1,null))    as mistake_cnt                   --错发量
            ,count(if(mistake_type=1,1,null))  as nofst_code_cnt                --无一段码
            ,count(if(mistake_type=2,1,null))  as no_route_cnt                  --无规划路由
            ,count(if(mistake_type=3,1,null))  as dispatch_cnt                  --调度指令  
            ,count(if(mistake_type=4,1,null))  as temp_tran_cnt                 --临时转运
            ,count(if(mistake_type=5,1,null))  as fstcode_err_cnt               --一段码异常
            ,count(if(mistake_type=6,1,null))  as up_bagging_err_cnt            --上环节建包异常
            ,count(if(mistake_type=7,1,null))  as equipment_factor_cnt          --交叉带建包
            ,count(if(mistake_type=8,1,null))  as return_cnt_cnt                --退回件
            ,count(if(mistake_type=9,1,null))  as send_on_cnt                   --转寄件
            ,count(if(mistake_type=10,1,null)) as delete_scan_cnt               --扫描删除
            ,count(if(mistake_type=11,1,null)) as human_factor_cnt              --人为因素
            ,count(if(mistake_type=13,1,null)) as fst_code_no_destination_cnt   --一段码无目的中心
            ,count(if(mistake_type=12,1,null)) as err_load_cnt                  --错装            
            ,dt
            ,provider_id                
            ,provider_desc              
       from jms_dm.dm_transfer_mistake_send_detail_dt 
      where dt = '{{ execution_date | cst_ds }}'
      group by dt,center_code,ordersource_name,date_time,provider_id,provider_desc,virt_code,virt_name,agent_code,agent_name,center_name
       ) t1 
distribute by dt;